{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7203c7a5-1855-42b7-b8bb-88ca081b659c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0d5f1a51-d365-41f7-aa9a-070667a0a2f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "demand_train_A = 'data/demand_train_A.csv'\n",
    "geo_topo = 'data/geo_topo.csv'\n",
    "inventory_info_A = 'data/inventory_info_A.csv'\n",
    "product_topo = 'data/product_topo.csv'\n",
    "weight_A = 'data/weight_A.csv'\n",
    "\n",
    "demand_train_A = pd.read_csv(demand_train_A)\n",
    "geo_topo = pd.read_csv(geo_topo)\n",
    "inventory_info_A = pd.read_csv(inventory_info_A)\n",
    "product_topo = pd.read_csv(product_topo)\n",
    "weight_A = pd.read_csv(weight_A)\n",
    "\n",
    "demand_test_A = 'data/demand_test_A.csv'\n",
    "\n",
    "demand_test_A = pd.read_csv(demand_test_A)\n",
    "\n",
    "dfs = [demand_train_A,geo_topo,inventory_info_A,product_topo,weight_A,demand_test_A]\n",
    "for df in dfs:\n",
    "    if 'Unnamed: 0' in df.columns:\n",
    "        df.drop(columns='Unnamed: 0',inplace=True)\n",
    "    if 'ts' in df.columns:\n",
    "        df = df.sort_values(by='ts')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2f128baa-6c71-4d51-b7bd-7a8bb9c55ca1",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_ts_start = demand_test_A.ts.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "53832097-6612-4962-ae57-ac86a83732e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_data = pd.concat([demand_train_A,demand_test_A])\n",
    "all_data = all_data.sort_values(by='ts')\n",
    "all_data = all_data.reset_index().drop(columns='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "96d8c02f-1517-4134-a89d-732f70f61782",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unit</th>\n",
       "      <th>ts</th>\n",
       "      <th>qty</th>\n",
       "      <th>geography_level</th>\n",
       "      <th>geography</th>\n",
       "      <th>product_level</th>\n",
       "      <th>product</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9b8f48bacb1a63612f3a210ccc6286cc</td>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>11926.8286</td>\n",
       "      <td>geography_level_3</td>\n",
       "      <td>36ab7b000da26b0547bfc3c3fdf143dc</td>\n",
       "      <td>product_level_2</td>\n",
       "      <td>5cc8015f03554313900f069182bdaf9c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4d3ca213b639541c5ba4cf8a69b1e1ed</td>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>628.1582</td>\n",
       "      <td>geography_level_3</td>\n",
       "      <td>7ac6047d36cb2b463fc6b483cc32da60</td>\n",
       "      <td>product_level_2</td>\n",
       "      <td>5cc8015f03554313900f069182bdaf9c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06531cd4188630ce2497cd9983aacf5e</td>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>6515.8894</td>\n",
       "      <td>geography_level_3</td>\n",
       "      <td>5f82497e6ba74177eacda9f48d2ebb8f</td>\n",
       "      <td>product_level_2</td>\n",
       "      <td>5cc8015f03554313900f069182bdaf9c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>326cb18b045e5baefa90bbc2e8d52a32</td>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>12311.5442</td>\n",
       "      <td>geography_level_3</td>\n",
       "      <td>60de5fa40aaaf2850711bc5269f91476</td>\n",
       "      <td>product_level_2</td>\n",
       "      <td>5cc8015f03554313900f069182bdaf9c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>04b155599672da2f35abd187df4b7d3f</td>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>11534.1116</td>\n",
       "      <td>geography_level_3</td>\n",
       "      <td>36ab7b000da26b0547bfc3c3fdf143dc</td>\n",
       "      <td>product_level_2</td>\n",
       "      <td>ae7d2d22786beebc5981b93c226e2aba</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               unit          ts         qty  \\\n",
       "0  9b8f48bacb1a63612f3a210ccc6286cc  2018-06-04  11926.8286   \n",
       "1  4d3ca213b639541c5ba4cf8a69b1e1ed  2018-06-04    628.1582   \n",
       "2  06531cd4188630ce2497cd9983aacf5e  2018-06-04   6515.8894   \n",
       "3  326cb18b045e5baefa90bbc2e8d52a32  2018-06-04  12311.5442   \n",
       "4  04b155599672da2f35abd187df4b7d3f  2018-06-04  11534.1116   \n",
       "\n",
       "     geography_level                         geography    product_level  \\\n",
       "0  geography_level_3  36ab7b000da26b0547bfc3c3fdf143dc  product_level_2   \n",
       "1  geography_level_3  7ac6047d36cb2b463fc6b483cc32da60  product_level_2   \n",
       "2  geography_level_3  5f82497e6ba74177eacda9f48d2ebb8f  product_level_2   \n",
       "3  geography_level_3  60de5fa40aaaf2850711bc5269f91476  product_level_2   \n",
       "4  geography_level_3  36ab7b000da26b0547bfc3c3fdf143dc  product_level_2   \n",
       "\n",
       "                            product  \n",
       "0  5cc8015f03554313900f069182bdaf9c  \n",
       "1  5cc8015f03554313900f069182bdaf9c  \n",
       "2  5cc8015f03554313900f069182bdaf9c  \n",
       "3  5cc8015f03554313900f069182bdaf9c  \n",
       "4  ae7d2d22786beebc5981b93c226e2aba  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e066d47-dd7a-4a49-b256-17e94899b8c6",
   "metadata": {},
   "source": [
    "### 预测未来需求量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c4e23d68-83ad-4ab5-a786-63617a02ee93",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-15-bb7a9c5c9b63>:15: FutureWarning: Series.dt.weekofyear and Series.dt.week have been deprecated.  Please use Series.dt.isocalendar().week instead.\n",
      "  submission['weekofyear'] = submission['dt'].dt.weekofyear\n"
     ]
    }
   ],
   "source": [
    "## 真实值串14天\n",
    "submission = demand_test_A\n",
    "submission['yesterday_qty'] = submission.groupby('unit')['qty'].shift(1).fillna(method='ffill').reset_index().sort_index().set_index('index')\n",
    "\n",
    "submission['diff_1'] = submission['qty'] - submission['yesterday_qty']\n",
    "submission['qty'] = submission['diff_1']\n",
    "# submission.loc[submission['qty']<0,'qty']=0\n",
    "\n",
    "submission['shift_14']=submission.groupby('unit')['qty'].shift(-14).fillna(0).reset_index().sort_index().set_index('index')\n",
    "submission = submission[['unit','ts','shift_14']].rename(columns={'shift_14':'qty'})\n",
    "\n",
    "\n",
    "# 按照7天聚合\n",
    "submission['dt'] = pd.to_datetime(submission['ts'])\n",
    "submission['weekofyear'] = submission['dt'].dt.weekofyear\n",
    "submission['year'] = submission['dt'].dt.year\n",
    "submission_week = submission.copy()\n",
    "submission_week = submission_week.groupby(['weekofyear','year','unit'],as_index=False).sum()\n",
    "submission_week['sum_qty'] = submission_week['qty']\n",
    "submission = pd.merge(submission_week,submission,on = ['weekofyear','year','unit'])\n",
    "submission['dayofweek'] = submission['dt'].dt.dayofweek\n",
    "submission = submission[submission['dayofweek']==0]\n",
    "submission = submission[['unit','ts','sum_qty']].rename(columns={'sum_qty':'qty'})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32ceb846-b4f9-4c8b-afb7-5ce16fe0573b",
   "metadata": {},
   "source": [
    "### 根据未来需求消耗掉初始库存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "14263507-d5d0-4c5a-84e5-d1d9dd77c836",
   "metadata": {},
   "outputs": [],
   "source": [
    "init_inventory = inventory_info_A.set_index(['unit'])['qty'].to_dict()\n",
    "def consume_init_inventory(arr,init_val):\n",
    "    remain = init_val\n",
    "    i = 0\n",
    "    while remain>0 and i<len(arr):\n",
    "        arr[i] = max(0,arr[i]-remain)\n",
    "        remain -= arr[i]\n",
    "        i+=1\n",
    "    return arr\n",
    "\n",
    "r = []\n",
    "for i,group in submission.groupby('unit'):\n",
    "\n",
    "    unit = group['unit'].values[0]\n",
    "    init_val = init_inventory[unit]\n",
    "    \n",
    "    group = group.sort_values(by='ts')\n",
    "    qty_list = group['qty'].values\n",
    "    qty_list = consume_init_inventory(qty_list,init_val)\n",
    "    group['qty'] = qty_list\n",
    "    r.append(group)\n",
    "\n",
    "    \n",
    "submission = pd.concat(r)    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0fa77c16-3cda-4f15-b6c8-ddcc0eaceb4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "submission.to_csv('submission.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ea9b0fa2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unit</th>\n",
       "      <th>ts</th>\n",
       "      <th>qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3792</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-03-08</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8216</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-03-15</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12640</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-03-22</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17064</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-03-29</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21488</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-04-05</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48025</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-05-10</td>\n",
       "      <td>15.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52449</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-05-17</td>\n",
       "      <td>1002.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56873</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-05-24</td>\n",
       "      <td>-0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61297</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61935</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-06-07</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8848 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   unit          ts          qty\n",
       "3792   0025accbb2e3dfbfe6f5b3a4562bdee0  2021-03-08     0.000000\n",
       "8216   0025accbb2e3dfbfe6f5b3a4562bdee0  2021-03-15     0.000000\n",
       "12640  0025accbb2e3dfbfe6f5b3a4562bdee0  2021-03-22     0.000000\n",
       "17064  0025accbb2e3dfbfe6f5b3a4562bdee0  2021-03-29     0.000000\n",
       "21488  0025accbb2e3dfbfe6f5b3a4562bdee0  2021-04-05     0.000000\n",
       "...                                 ...         ...          ...\n",
       "48025  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-05-10    15.333333\n",
       "52449  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-05-17  1002.333333\n",
       "56873  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-05-24    -0.333333\n",
       "61297  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-05-31     0.000000\n",
       "61935  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-06-07     0.000000\n",
       "\n",
       "[8848 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "submission"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "914c8da3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unit</th>\n",
       "      <th>ts</th>\n",
       "      <th>qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3792</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-03-08</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8216</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-03-15</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12640</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-03-22</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17064</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-03-29</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21488</th>\n",
       "      <td>0025accbb2e3dfbfe6f5b3a4562bdee0</td>\n",
       "      <td>2021-04-05</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48025</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-05-10</td>\n",
       "      <td>15.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52449</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-05-17</td>\n",
       "      <td>1002.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56873</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-05-24</td>\n",
       "      <td>-0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61297</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61935</th>\n",
       "      <td>ffddc0dbb7fa28b00a5b6ddc8e7e317c</td>\n",
       "      <td>2021-06-07</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8848 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   unit          ts          qty\n",
       "3792   0025accbb2e3dfbfe6f5b3a4562bdee0  2021-03-08     0.000000\n",
       "8216   0025accbb2e3dfbfe6f5b3a4562bdee0  2021-03-15     0.000000\n",
       "12640  0025accbb2e3dfbfe6f5b3a4562bdee0  2021-03-22     0.000000\n",
       "17064  0025accbb2e3dfbfe6f5b3a4562bdee0  2021-03-29     0.000000\n",
       "21488  0025accbb2e3dfbfe6f5b3a4562bdee0  2021-04-05     0.000000\n",
       "...                                 ...         ...          ...\n",
       "48025  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-05-10    15.333333\n",
       "52449  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-05-17  1002.333333\n",
       "56873  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-05-24    -0.333333\n",
       "61297  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-05-31     0.000000\n",
       "61935  ffddc0dbb7fa28b00a5b6ddc8e7e317c  2021-06-07     0.000000\n",
       "\n",
       "[8848 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "submission"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7bf7dee9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
